//École de Technologie Supérieure (ÉTS)
//LOG720 - Laboratoire 2  - J2EE
//Date de remise: 2011-11-14
//Auteurs:
//Jonathan Aubuchon (AUBJ18038704)
//Jeff Grenier (GREJ02098703)
//Francis Paré (PARF21038708)

package ca.etsmtl.log720.lab2.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import ca.etsmtl.log720.lab2.beans.Infraction;

/*
 * Data Acces Object pour les infractions
 */
public class InfractionDAO {
	static InfractionDAO instance=new InfractionDAO();
	private InfractionDAO(){}
	
	/**
	 * retourne l'instance du DAO
	 * @return 
	 */
	public static InfractionDAO getInstance(){
		return instance;
	}
	
	/**
	 * Retourne la totalité des infractions sous la forme d'une collection d'infractions
	 * @return 
	 */
	public ArrayList<Infraction> selectInfractions(){
		
		Connection dataSourceConnection= DBConnection.getInstance().getConnection();
		ArrayList<Infraction> linkedInfractions=null;
		
		try {
			PreparedStatement selectStatement = dataSourceConnection.prepareStatement ("SELECT * FROM infraction");
			
			ResultSet infractionSet = selectStatement.executeQuery();

			linkedInfractions = new ArrayList<Infraction>();
			while (infractionSet.next()) { 
				int id = infractionSet.getInt("id");
				int niveau = infractionSet.getInt("niveau");
				String description = infractionSet.getString("description");
				
				Infraction  infraction  = new Infraction ();
				infraction.setID(id);
				infraction.setNiveau(niveau);
				infraction.setDescription(description);
				linkedInfractions.add (infraction);
			}
						
				
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return linkedInfractions;
	}
	
	/**
	 * Retourne le niveau de l'infraction ayant le plus haut niveau dans le dossier
	 */
	public int selectMaxNiveauInfraction(int dossierID){
		Connection dataSourceConnection= DBConnection.getInstance().getConnection();
		int maxNiveau = -1;
		try {
			PreparedStatement selectStatement = dataSourceConnection.prepareStatement (
			
					"SELECT MAX(niveau) as max FROM dossier_infraction "+
					"INNER JOIN infraction ON (dossier_infraction.idinfraction=infraction.id) "+
					"WHERE dossier_infraction.iddossier=?;");
			selectStatement.setInt(1, dossierID);
			
			ResultSet result = selectStatement.executeQuery();
			result.next();
			maxNiveau = result.getInt("max");
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return maxNiveau;
	}
	
	/**
	 * retourne la liste des infractions pour un dossier en particulier.
	 * @param dossierID
	 * @return
	 */
	public ArrayList<Infraction> selectInfractions(int dossierID){
		
		Connection dataSourceConnection= DBConnection.getInstance().getConnection();
		ArrayList<Infraction> linkedInfractions=null;
		
		try {
			
			PreparedStatement selectStatement = dataSourceConnection.prepareStatement (
					"SELECT * FROM dossier_infraction "+
					"INNER JOIN infraction ON (dossier_infraction.idinfraction=infraction.id) "+
					"WHERE dossier_infraction.iddossier=?;");
			selectStatement.setInt(1, dossierID);
			
			ResultSet infractionSet = selectStatement.executeQuery();

			linkedInfractions = new ArrayList<Infraction>();
			while (infractionSet.next()) { 
				int id = infractionSet.getInt("id");
				int niveau = infractionSet.getInt("niveau");
				String description = infractionSet.getString("description");
				
				Infraction  infraction  = new Infraction ();
				infraction.setID(id);
				infraction.setNiveau(niveau);
				infraction.setDescription(description);
				linkedInfractions.add (infraction);
			}
			
				
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return linkedInfractions;
	}

	
	
	/**
	 * Ajoute une nouvelle infraction au système.
	 * Retourne true si l'ajout a fonctionné, sinon false en cas d'échec
	 * @param infraction
	 * @return
	 */
	public boolean insertInfraction(Infraction infraction){
		Connection dataSourceConnection= DBConnection.getInstance().getConnection();
		try {
		
			PreparedStatement selectStatement = dataSourceConnection.prepareStatement (
					"INSERT INTO infraction(id,niveau,description) VALUES(DEFAULT,?,?)");

			selectStatement.setInt(1, infraction.getNiveau());
			selectStatement.setString(2, infraction.getDescription());
			
			selectStatement.execute();
				
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

}
